#!/usr/bin/python
# -*- coding: utf-8 -*-
from odbc import ODBC
from util.logger import process_log
from util.tools import calculate_date_day
import cx_Oracle


class ORACLE(ODBC):

    sys_db = []

    def text_convert(self, table, tb_id, field, field_id, delta, fmt):
        max_value = self.read_index(tb_id, field_id)
        max_value = calculate_date_day(max_value, fmt, delta)
        if not max_value:
            return ''
        where = '''where "%s"."%s" >= to_date('%s', 'YYYY-MM-DD HH24:MI:SS')''' % (table, field, max_value)
        return where

    def dump(self, database_name, table_name, fields_name, where, tb_id, return_cursor=False, breakpoint=-1):
        fields_with_table = map(lambda x: self.field_join(x[0], x[1]), [(table_name, field) for field in fields_name])
        fields_sql = ', '.join(fields_with_table)
        sql = '''select %s from "%s" %s ''' % (fields_sql, table_name, where)
        cursor = self.cursor

        if return_cursor:
            process_log().info("Fetch data by sql: %s" % sql)
            cursor.execute(sql)
            return cursor

    def count(self, database, table, where):
        """
            Get the count of sync by giving info
        Args:
            database (str):
            table (str):
            where (str):

        Returns:
            sql_count (int):
        """
        sql = 'select count(*) from "%s" %s' % (table, where)
        process_log().info('Fetch rows count by sql statement: %s' % sql)
        self.cursor.execute(sql)
        res = self.cursor.fetchall()
        return str(res[0][0]) if res else '0'

    def max_value(self, database, table, index, where):
        sql = 'select max("%s"."%s") ' \
              'from "%s" %s' % (table, index, table, where)
        process_log().info('Fetch max value by sql: %s' % sql)
        self.cursor.execute(sql)
        res = self.cursor.fetchall()
        return str(res[0][0]) if res else ''

    def field_join(self, table, field):
        raw_type = self.field_dict.get(table, {}).get(field, {}).get('raw_type', '').lower()
        if raw_type == 'float':
            return u'"%s"."%s"' % (table, field)
        else:
            return u'"%s"."%s"' % (table, field)

    def fetch_schema(self, database, table_name):
        if not (database and table_name):
            process_log().error('table name and database is required!')
            return
        sql = '''
            SELECT DISTINCT TABLE_NAME,B_NAME,DATA_TYPE,COLUMN_KEY FROM (
                SELECT
                    col.COLUMN_NAME AS A_NAME, 'PRI' AS COLUMN_KEY
                FROM
                    all_cons_columns col,
                    all_constraints con
                WHERE
                    col.constraint_name = con.constraint_name
                AND con.constraint_type = 'P'
                AND col.table_name='%s') A
            RIGHT JOIN(
                SELECT COLUMN_NAME AS B_NAME, DATA_TYPE, TABLE_NAME from ALL_TAB_COLUMNS WHERE TABLE_NAME='%s') B
            ON A.A_NAME = B.B_NAME''' % (table_name, table_name)
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    def __init__(self, connect_info):
        ODBC.__init__(self, connect_info)
        if 'port' not in connect_info:
            connect_info['port'] = str(1521)

        connectionstr = "%s/%s@%s:%s/%s" % (
            connect_info['uid'], connect_info['pwd'], connect_info['server'], connect_info['port'],
            connect_info['database'])
        connectionstr_back = "%s/%s@%s" % (
            connect_info['uid'], connect_info['pwd'],
            connect_info['database'])
        self.database = connect_info['database']

        try:
            self.cursor = cx_Oracle.connect(connectionstr_back).cursor()
        except Exception:
            self.cursor = cx_Oracle.connect(connectionstr).cursor()

    def fetch_databases(self):
        return [('%s' % self.database,)]

    def fetch_tables(self, database, view=True):
        sql = """
                SELECT DISTINCT
                    view_name AS table_name
                FROM
                    user_views
                UNION
                    (
                        SELECT DISTINCT
                            table_name
                        FROM
                            user_tables
                    )
                ORDER BY
                    table_name"""
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    def fetch_fields(self, database, table):
        sql = "SELECT DISTINCT COLUMN_NAME, DATA_TYPE FROM ALL_TAB_COLUMNS where TABLE_NAME = '%s' " % table
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    def fetch_record_count(self, database, table):
        sql = '''select count(1) from "%s"''' % table
        self.cursor.execute(sql)
        return self.cursor.fetchall()
